'Versionen:
'17.05.2007/DH/Neuanlage

Imports Microsoft.VisualBasic
Imports System
Imports System.WEB
Imports System.ComponentModel
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Data
Imports System.Data.OleDb

Public Class cTbVorlagen
    Dim mstrConnString As String
    Public Function GetDataSet(Optional ByVal StrSelect As String = "*", Optional ByVal strWhere As String = "vnr >= 0", Optional ByVal strOrder As String = "vnr") As DataSet
        Dim Sql As String = "SELECT " & StrSelect & " FROM tbl_vorlagen WHERE " & strWhere
        If strOrder <> "" Then Sql += " ORDER BY " & strOrder
        'Dim myConn2 As New S qlClient.SqlConnection(mstrConnString)
        Dim myConn2 As New OleDbConnection(mstrConnString)
        'Dim myCmd2 As New S qlClient.SqlCommand
        Dim myCmd2 As New OleDbCommand
        myCmd2.Connection = myConn2
        'Dim myAdapter As New S qlClient.SqlDataAdapter()
        Dim myAdapter As New OleDbDataAdapter()
        myAdapter.SelectCommand = myCmd2

        GetDataSet = New DataSet
        myCmd2.CommandText = Sql
        myAdapter.Fill(GetDataSet, "Table")

        myConn2.Close()
    End Function


    Public Function GetFelder(ByVal intID As Integer, Optional ByVal pWhere As String = "") As DataView
        Dim myConn As New OleDbConnection(mstrConnString)
        myConn.Open()

        ' Kommando-Objekt vorbereiten
        Dim cmd As New OleDbCommand()
        cmd.Connection = myConn
        ' Adapter-Objekt vorbereiten
        Dim myAdapter As New OleDbDataAdapter()
        myAdapter.SelectCommand = cmd
        ' DataSet-Objekt vorbereiten
        Dim myDS As New DataSet()

        ' Tabelle "Pages" einlesen
        Dim Sql As String
        If pWhere <> "" Then
            Sql = "SELECT * FROM tbl_vorlagen WHERE " & pWhere
        Else
            Sql = "SELECT * FROM tbl_vorlagen WHERE vnr = " & intID
        End If
        cmd.CommandText = Sql
        myAdapter.Fill(myDS, "Pages")

        GetFelder = New DataView(myDS.Tables("Pages"))
        myDS.Dispose()
        myConn.Close()
    End Function

    Public Sub Update(ByVal intID As Integer, _
                        ByVal Art As String, _
                        ByVal Wert As String, _
                        Optional ByVal Zusatz As String = Nothing)
        Dim Sql As String = ""
        If Not Art = Nothing Then Sql += ", Art = '" & Art & "'"
        If Not Wert = Nothing Then Sql += ", Wert = '" & Wert & "'"
        If Not Zusatz = Nothing Then Sql += ", Zusatz = '" & Zusatz & "'"
        
        If Sql.Length > 0 Then
            Sql = "UPDATE tbl_vorlagen SET " & Sql.Substring(2)
            Sql += " WHERE vnr = " & intID
            Dim myConn As New OleDbConnection(mstrConnString)
            Dim Cmd As New OleDbCommand(Sql, myConn)
            myConn.Open()
            Cmd.ExecuteReader()
            myConn.Close()
        End If
    End Sub

    Public Sub Delete(ByVal intID As Integer)
        Dim strSQL As String = "DELETE * FROM tbl_vorlagen WHERE vnr = " & intID & ";"
        Dim dconn As New OleDbConnection(mstrConnString)
        Dim iCmd As New OleDbCommand(strSQL, dconn)
        dconn.Open()
        iCmd.ExecuteReader()
        dconn.Close()
    End Sub

    Public Sub Insert(ByVal Art As String, _
                        ByVal Wert As String, _
                        Optional ByVal Zusatz As String = Nothing)
        Dim F As String = ""
        Dim W As String = ""
        Dim Sql As String = ""
        If Not Art = Nothing Then F += ",Art" : W += ",'" & Art & "'"
        If Not Wert = Nothing Then F += ",Wert" : W += ",'" & Wert & "'"
        If Not Zusatz = Nothing Then F += ",Zusatz" : W += ",'" & Zusatz & "'"
        
        If F.Length > 0 Then
            Sql = "INSERT INTO tbl_vorlagen (" & F.Substring(1) & ") VALUES(" & W.Substring(1) & ")"
            Dim myConn As New OleDbConnection(mstrConnString)
            Dim Cmd As New OleDbCommand(Sql, myConn)
            myConn.Open()
            Cmd.ExecuteReader()
            myConn.Close()
        End If
    End Sub
    Public Function GetLetzteId() As Integer
        Dim sql As String
        sql = "SELECT MAX(vnr) AS IhreID FROM tbl_vorlagen"
        Dim myConn As New OleDbConnection(mstrConnString)
        myConn.Open()
        Dim cmd As New OleDbCommand(sql, myConn)
        Dim letzteID As Integer = cmd.ExecuteScalar()
        myConn.Close()
        Return letzteID
    End Function
    Public Sub New(ByVal strConnString As String)
        mstrConnString = strConnString
    End Sub
End Class
